from flask import Flask, render_template, jsonify, request
from flask_cors import CORS
import pymysql
import json
from datetime import datetime, timedelta
from config import Config

app = Flask(__name__)
app.config.from_object(Config)
CORS(app)

def get_db_connection():
    """获取数据库连接"""
    return pymysql.connect(
        host=app.config['MYSQL_HOST'],
        port=app.config['MYSQL_PORT'],
        user=app.config['MYSQL_USER'],
        password=app.config['MYSQL_PASSWORD'],
        database=app.config['MYSQL_DATABASE'],
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )

@app.route('/')
def index():
    """首页路由"""
    return render_template('index.html')

@app.route('/api/login', methods=['POST'])
def login():
    """用户登录接口"""
    try:
        data = request.get_json()
        username = data.get('username')
        password = data.get('password')
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute(
            "SELECT * FROM users WHERE username = %s AND password = %s",
            (username, password)
        )
        user = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if user:
            return jsonify({
                'success': True,
                'message': '登录成功',
                'user': {
                    'id': user['id'],
                    'username': user['username'],
                    'email': user['email']
                }
            })
        else:
            return jsonify({
                'success': False,
                'message': '用户名或密码错误'
            }), 401
            
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'登录失败: {str(e)}'
        }), 500

@app.route('/api/products')
def get_products():
    """获取商品列表接口"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute("SELECT * FROM products ORDER BY id")
        products = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': products
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取商品列表失败: {str(e)}'
        }), 500

@app.route('/api/sales/trend')
def get_sales_trend():
    """获取销售趋势数据"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 获取最近7天的销售数据
        cursor.execute("""
            SELECT DATE(sale_date) as date, SUM(amount) as total_amount, COUNT(*) as order_count
            FROM sales 
            WHERE sale_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
            GROUP BY DATE(sale_date)
            ORDER BY date
        """)
        sales_data = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': sales_data
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取销售趋势失败: {str(e)}'
        }), 500

@app.route('/api/sales/category')
def get_sales_by_category():
    """获取按类别统计的销售数据"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT p.category, SUM(s.amount) as total_amount, SUM(s.quantity) as total_quantity
            FROM sales s
            JOIN products p ON s.product_id = p.id
            GROUP BY p.category
            ORDER BY total_amount DESC
        """)
        category_data = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': category_data
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取类别销售数据失败: {str(e)}'
        }), 500

@app.route('/api/sales/region')
def get_sales_by_region():
    """获取按地区统计的销售数据"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT region, SUM(amount) as total_amount, COUNT(*) as order_count
            FROM sales
            GROUP BY region
            ORDER BY total_amount DESC
        """)
        region_data = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': region_data
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取地区销售数据失败: {str(e)}'
        }), 500

@app.route('/api/dashboard/stats')
def get_dashboard_stats():
    """获取仪表板统计数据"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 总销售额
        cursor.execute("SELECT SUM(amount) as total_sales FROM sales")
        total_sales = cursor.fetchone()['total_sales'] or 0
        
        # 总订单数
        cursor.execute("SELECT COUNT(*) as total_orders FROM sales")
        total_orders = cursor.fetchone()['total_orders'] or 0
        
        # 商品总数
        cursor.execute("SELECT COUNT(*) as total_products FROM products")
        total_products = cursor.fetchone()['total_products'] or 0
        
        # 今日销售额
        cursor.execute("""
            SELECT SUM(amount) as today_sales 
            FROM sales 
            WHERE DATE(sale_date) = CURDATE()
        """)
        today_sales = cursor.fetchone()['today_sales'] or 0
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': {
                'total_sales': float(total_sales),
                'total_orders': total_orders,
                'total_products': total_products,
                'today_sales': float(today_sales)
            }
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取统计数据失败: {str(e)}'
        }), 500

@app.route('/api/products/top')
def get_top_products():
    """获取热销商品数据"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT p.name, p.category, SUM(s.quantity) as total_quantity, SUM(s.amount) as total_amount
            FROM sales s
            JOIN products p ON s.product_id = p.id
            GROUP BY p.id, p.name, p.category
            ORDER BY total_quantity DESC
            LIMIT 10
        """)
        top_products = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': top_products
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取热销商品失败: {str(e)}'
        }), 500

if __name__ == '__main__':
    print("=" * 50)
    print("数据可视化大屏系统启动中...")
    print("=" * 50)
    print("访问地址: http://localhost:5000")
    print("API文档: http://localhost:5000/api/")
    print("=" * 50)
    app.run(debug=True, host='0.0.0.0', port=5000) 